#!C:/Perl/bin/perl.exe -w
# Display an ALEKS item mastery report for a single student.
# Specify a particular extracted ALEKS data record by its batch key
# (recorded in the report table in the repository database).
# Ie, our URL will end with ?report=<batchkey>

use CGI;
use DBI;
use strict;

# Send stderr to stdout for debugging.
# Open or note debug and log files (currently unused).
*STDERR = *STDOUT;
open DEBUG, ">", "debug.txt" or die "Can't open debug log: $!";
my $logFile = "ALEKSlog.txt";

sub percent {
    # Return a percentage. Avoid dividing by zero.
    my $numerator = shift;
    my $denominator = shift;
    if ($denominator == 0) {
        return 0;
    } else {
        return $numerator / $denominator * 100;
    }
}

sub percentPie {
    # Create a pie chart showing a single percentage value (of items mastered), at a specified size (width) in pixels.
    # print percentPie(50, 70);  # shows 50% items mastered in a 70x70 pixel graphic.
    my $percent = shift;
    my $size = shift;
    if (!defined $size) { $size = 70 }
    $size = sprintf('%.0f', $size);
    my $data = sprintf('%.0f', $percent) . ',' . sprintf('%.0f', 100 - $percent);
    my $chartPiece1 = '<img class="pieImg" src="http://chart.apis.google.com/chart?chs=SZxSZ&cht=p&chco=008000&chd=t:';
    my $chartPiece2 = '&chp=4.71" width="SZ" height="SZ" alt="percentage of items mastered" />';
    my $html = $chartPiece1 . $data . $chartPiece2;
    $html =~ s/SZ/$size/g;
    return $html;
}

# Style HTML with CSS rules.
# H1 - course name
# H2 - student name and ALEKS record name
# H3 - strand name
# H4 - standard designator, eg 5.N.1, and description
my $styleBlock=<<EOT;
td { vertical-align: top }
.standardBlock { margin-left: 0.25in }
.itemColumn { padding-right: 15px; text-align: left }
caption { text-align: left }
.mastery { font-style: italic }
.pieImg { vertical-align: middle }
EOT

# Include Javascript.
my $javascript=<<EOT;
function studentSelect() {
    alert('You selected a student!');
}
EOT

# Get the HTTP request, keep it in $q.
my $q = CGI->new;
# Extract parameters.
my $batch = $q->param('report');
# Begin a response.
print $q->header, $q->start_html(-title=>'ALEKS mastery report for MA standards',
    -style=>{'-code'=>$styleBlock},
    -script=>$javascript);

# Open the ALEKS repository (SQLite database).
my $dbfile = 'aleks.sqlite';  # in the current directory
my $db = DBI->connect("dbi:SQLite:dbname=$dbfile","","")  or die $DBI::errstr;
my $sql;  # a temporary holder for SQL statement strings
my $stmt; # a temporary holder for executing SQL statements
my @row;  # a temporary holder for fetched rows

sub mastered {
    # Return a list of mastered or unmastered ALEKS items under a particular
    # MA standard from a particular ALEKS student report.
    my $reportedStandard = shift;  # rowid from reportedStandard table
    my $mastered = shift;          # true or false
    $mastered = ($mastered ? 1 : 0);  # value used in mastery table
    my @item;  # will hold item text strings
    my $sql = 'select text from item join mastery on item.rowid = mastery.item'
        . ' where reportedStandard = ? and mastered = ?;';
    my $stmt = $db->prepare($sql) or die $db->errstr;
    $stmt->execute($reportedStandard, $mastered) or die $stmt->errstr;
    my @row;
    while (@row = $stmt->fetchrow_array) {
        push @item, @row;
    }
    return @item;
}


# Fetch info for this report batch.
my ($reportId, $courseId, $studentId, $record, $teacher);
$sql = 'select report.rowid, course, student, record, teacher.name'
    . ' from report join teacher on report.teacher = teacher.rowid'
    . ' where batch = ?;';
$stmt = $db->prepare($sql) or die $db->errstr;
$stmt->execute($batch) or die $stmt->errstr;
if (@row = $stmt->fetchrow_array) {
    $reportId = $row[0];
    $courseId = $row[1];
    $studentId = $row[2];
    $record = $row[3];
    $teacher = $row[4];
}
$stmt->finish;

if (defined($reportId)) {
    # Print course and teacher name.
    my $courseName;
    $sql = 'select name from course where rowid = ?;';
    $stmt = $db->prepare($sql) or die $db->errstr;
    $stmt->execute($courseId) or die $stmt->errstr;
    if (@row = $stmt->fetchrow_array) {
        $courseName = $row[0];
    }
    $stmt->finish;
    print $q->h1($courseName, '(' . $teacher . ')');

    # Print student name and the ALEKS record name.
    my $studentName;
    $sql = 'select name from student where rowid = ?;';
    $stmt = $db->prepare($sql) or die $db->errstr;
    $stmt->execute($studentId) or die $stmt->errstr;
    if (@row = $stmt->fetchrow_array) {
        $studentName = $row[0];
    }
    $stmt->finish;
    print $q->h2($studentName, '<br>', $record);

    # Print overall mastery, with a pie chart.
    # Add up mastery figures for each standard reported in this batch.
    my ($nItems, $nMastered);
    $nItems = $nMastered = 0;
    $sql = 'select nItems, nMastered from reportedStandard where report = ?;';
    $stmt = $db->prepare($sql) or die $db->errstr;
    $stmt->execute($reportId) or die $stmt->errstr;
    while (@row = $stmt->fetchrow_array) {
        $nItems += $row[0];
        $nMastered += $row[1];
    }
    $stmt->finish;
    # Print.
    print 
        $q->table(
            $q->Tr(
                $q->td( {-class=>'mastery'}, $nMastered . ' of ' . $nItems . ' ALEKS items mastered, '
                    . sprintf('%.0f', percent($nMastered, $nItems)) . '%.' ),
                $q->td( percentPie(percent($nMastered, $nItems), 100) )
            )
        );

    # Print a section for each MA strand that is represented in this batch.
    my ($strandNum, $strandSym, $strandText, $strandStmt);
    $sql = 'select number, symbol, text from strand order by number;';
    $strandStmt = $db->prepare($sql) or die $db->errstr;
    $strandStmt->execute() or die $strandStmt->errstr;
    while (@row = $strandStmt->fetchrow_array) {
        ($strandNum, $strandSym, $strandText) = @row;

        # Summarize items mastered at the strand level.
        # Look up figures for each standard reported for this strand.
        my ($nItems, $nMastered);
        $nItems = $nMastered = 0;
        $sql = 'select nItems, nMastered'
            . ' from reportedStandard join standard on reportedStandard.standard = standard.rowid'
            . ' where report = ? and strand = ?;';
        $stmt = $db->prepare($sql) or die $db->errstr;
        $stmt->execute($reportId, $strandNum) or die $stmt->errstr;
        while (@row = $stmt->fetchrow_array) {
            $nItems += $row[0];
            $nMastered += $row[1];
        }
        $stmt->finish;
        if ($nItems > 0) {  # ignoring strands that aren't represented
            # Print the strand name and mastery statement to the left of the pie.
            print $q->hr(), $q->table( 
                $q->Tr(
                    $q->td( 
                        $q->h3($strandText),
                        $q->p({-class=>'mastery'}, $nMastered . ' of ' . $nItems . ' ALEKS items mastered, '
                            . sprintf('%.0f', percent($nMastered, $nItems)) . '%.'
                        )
                    ),
                    $q->td( percentPie(percent($nMastered, $nItems), 100) )
                )
            );
        }

        # Print a subsection for each MA standard that is represented in this batch.
        my ($standardGrade, $standardNum, $standardText, $reportedStandard);
        $nItems = $nMastered = 0;  # reuse these variables
        $sql = 'select grade, number, text, nItems, nMastered, reportedStandard.rowid'
            . ' from standard join reportedStandard on standard.rowid = reportedStandard.standard'
            . ' where strand = ? and reportedStandard.report = ?;';
        my $standardStmt = $db->prepare($sql) or die $db->errstr;
        $standardStmt->execute($strandNum, $reportId) or die $standardStmt->errstr;
        while (@row = $standardStmt->fetchrow_array) {
            ($standardGrade, $standardNum, $standardText, $nItems, $nMastered, $reportedStandard) = @row;
            print $q->h4( $q->table(
                $q->Tr(
                    $q->td($standardGrade . '.' . $strandSym . '.' . $standardNum), 
                    $q->td({-style=>'font-weight: normal'}, $standardText)
                )
            ));
            print $q->p( {-class=>'mastery standardBlock'}, $nMastered . ' of ' . $nItems . ' ALEKS items mastered', percentPie(percent($nMastered, $nItems), 50));
            print $q->table({-class=>'standardBlock'},
                    $q->Tr( $q->th({-class=>'itemColumn'}, ['mastered', 'not mastered'])),
                    $q->Tr( $q->td({-class=>'itemColumn'}, [join('<br>', mastered($reportedStandard, 1)), join('<br>', mastered($reportedStandard, 0))]) )
            );

        }
        $standardStmt->finish;
    }
    $strandStmt->finish;

} else {
    print $q->p('I cannot find the student report you requested, ' . $batch . '.');
}

print $q->end_html;
